/**
 * Copyright (c) 2018 人人开源 All rights reserved.
 * <p>
 * https://www.renren.io
 * <p>
 * 版权所有，侵权必究！
 */

package org.edu.modules.util;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
/**
 * excel工具类
 *
 * @author Mark sunlightcs@gmail.com
 */
public class ExcelUtils {
    /**
     * excell 2003 表格后缀
     */
    public static final String EXCELL_2003_SUFFIX = "xls";
    /**
     * excell 2007 表格后缀
     */
    public static final String EXCELL_2007_SUFFIX = "xlsx";

    /**
     * Excel导出
     *
     * @param response  response
     * @param fileName  文件名
     * @param sheetName sheetName
     * @param list      数据List
     * @param pojoClass 对象Class
     */
    public static void exportExcel(HttpServletResponse response, String fileName, String sheetName, List<?> list,
                                   Class<?> pojoClass) throws IOException {
        if (StringUtils.isBlank(fileName)) {
            //当前日期
            fileName = DateUtilss.format(new Date());
        }

        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("UTF-8");
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), pojoClass).sheet(sheetName).doWrite(list);
    }

    /**
     * Excel导出，先sourceList转换成List<targetClass>，再导出
     *
     * @param response    response
     * @param fileName    文件名
     * @param sheetName   sheetName
     * @param sourceList  原数据List
     * @param targetClass 目标对象Class
     */
    public static void exportExcelToTarget(HttpServletResponse response, String fileName, String sheetName, List<?> sourceList,
                                           Class<?> targetClass) throws Exception {
        List targetList = new ArrayList<>(sourceList.size());
        for (Object source : sourceList) {
            Object target = targetClass.newInstance();
            BeanUtils.copyProperties(source, target);
            targetList.add(target);
        }

        exportExcel(response, fileName, sheetName, targetList, targetClass);
    }

    /**
     * <p>将Excel2003转成Json数据</p>
     *
     * @param inputStream 输入流
     * @return JSONArray 数组
     * @throws IOException            IO 异常
     * @throws InvalidFormatException 无效格式化异常
     * @author LanChaoHui
     * @date 2020/11/17 11:51
     * @since 0.0.1
     */
    public static JSONArray jsonExcel2003(InputStream inputStream) throws IOException, InvalidFormatException {
        Workbook workbook = WorkbookFactory.create(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
        return analysisData(sheet);
    }

    /**
     * <p>解析Excel2007转成JSON数据</p>
     *
     * @param inputStream 输入流
     * @return JSONArray 数组
     * @throws IOException IO 异常
     * @author LanChaoHui
     * @date 2020/11/17 11:54
     * @since 0.0.1
     */
    public static JSONArray jsonExcel2007(InputStream inputStream) throws IOException {
        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
        return analysisData(sheet);
    }

    /**
     * <p>判断是否是Excel2007</p>
     *
     * @param multipartFile 多文件
     * @return boolean true 是Excel 2007 false 不是Excel2007
     * @author LanChaoHui
     * @date 2020/11/17 11:55
     * @since 0.0.1
     */
    public static boolean isExcel2007(MultipartFile multipartFile) {
        String uploadName = multipartFile.getOriginalFilename();
        String suffix = uploadName.substring(uploadName.indexOf("."));
        System.out.println(".xlsx".equalsIgnoreCase(suffix));
        return ".xlsx".equalsIgnoreCase(suffix);
    }

    /**
     * <p>导出excel文件</p>
     *
     * @param response    响应
     * @param fileName    文件名
     * @param fileType    文件类型
     * @param inputStream 文件输入流
     * @author LanChaoHui
     * @date 2020/11/17 9:35
     * @since 0.0.1
     */
    public static void exportExcel(HttpServletResponse response, String fileName, String fileType, InputStream inputStream) throws IOException, InvalidFormatException {
        if (StringUtils.isBlank(fileName)) {
            fileName = DateUtilss.format(new Date());
        }
        Workbook workbook = null;
        if (fileType.equals(EXCELL_2003_SUFFIX)) {
            workbook = excel2003Handle(inputStream, response, fileName);
        }
        if (fileType.equals(EXCELL_2007_SUFFIX)) {
            workbook = excel2007Handle(inputStream, response, fileName);
        }
        ServletOutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.flush();
    }

    /**
     * <p>world 2007 响应头设置</p>
     *
     * @param inputStream 输入流
     * @param response    响应
     * @param fileName    文件名
     * @return Workbook
     * @author LanChaoHui
     * @date 2021/1/7 15:42
     * @since 0.0.1
     */
    private static Workbook excel2007Handle(InputStream inputStream, HttpServletResponse response, String fileName) throws IOException {
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("gbk"), "iso8859-1") + "." + EXCELL_2007_SUFFIX);
        response.setContentType("application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
        Workbook workbook = new XSSFWorkbook(inputStream);
        return workbook;
    }

    /**
     * <p>world 2003 响应头设置</p>
     *
     * @param inputStream 输入流
     * @param response    响应
     * @param fileName    文件名
     * @return Workbook
     * @author LanChaoHui
     * @date 2021/1/7 15:42
     * @since 0.0.1
     */
    private static Workbook excel2003Handle(InputStream inputStream, HttpServletResponse response, String fileName) throws IOException {
        response.setHeader("Content-Disposition", "attachment;filename=contacts" + new String(fileName.getBytes("gbk"), "iso8859-1") + "." + EXCELL_2003_SUFFIX);
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        return workbook;
    }

    /**
     * <p>解析数据</p>
     *
     * @param sheet excel 页数据
     * @return JSONArray json 数组数据
     * @author LanChaoHui
     * @date 2020/11/17 11:49
     * @since 0.0.1
     */
    private static JSONArray analysisData(Sheet sheet) {
        JSONArray jsonArray = new JSONArray();
        int rowEnd = sheet.getPhysicalNumberOfRows();
        String[] title = null;
        for (int i = 0; i < rowEnd; i++) {
            Row row = sheet.getRow(i);
            int cellstart = row.getFirstCellNum();
            int cellend = row.getLastCellNum();
            // 过滤出表头
            if (i == 0) {
                title = new String[cellend];
                for (int j = cellstart; j < cellend; j++) {
                    Cell cell = row.getCell(j);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    if (StringUtils.isNotBlank(cell.getStringCellValue())) {
                        title[j] = cell.getStringCellValue();
                    }
                }
                title = filterNull(title);
            } else {
                JSONObject jsonObject = new JSONObject();
                for (int j = 0; j < title.length; j++) {
                    Cell cell = row.getCell(j);
                    if(cell==null){
                        continue;
                    }
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    String value = cell.getStringCellValue();
                    if (StringUtils.isBlank(value) && jsonObject.isEmpty()) {
                        continue;
                    }
                    jsonObject.put(title[j], cell.getStringCellValue());
                }
                if (!jsonObject.isEmpty()) {
                    jsonArray.add(jsonObject);
                }
            }

        }
        return jsonArray;
    }

    /**
     * <p>为字符串宿主过滤空字符串，并返回</p>
     *
     * @param strList 字符串集合
     * @return 字符串数组
     * @author LanChaoHui
     * @date 2020/11/17 11:47
     * @since 0.0.1
     */
    private static String[] filterNull(String[] strList) {
        StringBuilder stringBuffer = new StringBuilder();
        for (int i = 0; i < strList.length; i++) {
            String str = strList[i];
            if (StringUtils.isNotBlank(str)) {
                stringBuffer.append(str);
                if (i != strList.length - 1) {
                    stringBuffer.append(";");
                }
            }
        }
        return stringBuffer.toString().split(";");
    }

    /**
     * <p>将EXCEL文件内的数据转换成JSONArray数组</p>
     *
     * @param multipartFile 多文件
     * @return JSONArray 数组
     * @throws IOException            IO 异常
     * @throws InvalidFormatException 无效格式化异常
     * @author LanChaoHui
     * @date 2020/11/17 11:56
     * @since 0.0.1
     */
    public static JSONArray excelToJson(MultipartFile multipartFile) throws IOException, InvalidFormatException {
        InputStream inputStream = multipartFile.getInputStream();
        return isExcel2007(multipartFile) ? jsonExcel2007(inputStream) : jsonExcel2003(inputStream);
    }

    /**
     * <p>写入excel</p>
     *
     * @param title sheet 名
     * @param data 需要填充到excel的数据
     * @param heads 表头
     * @param excelWriter Excel的写入对象
     * @param  cellWriteHandlers 表格处理
     * @author LanChaoHui
     * @date 2021/2/4 11:13
     * @since 0.0.1
     */
    public static void writeExcel(String title, List<?> data, List<List<String>> heads, ExcelWriter excelWriter, CellWriteHandler... cellWriteHandlers) {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        ExcelWriterSheetBuilder excelWriterSheetBuilder = EasyExcel.writerSheet(title);
        excelWriterSheetBuilder.head(heads);
        excelWriterSheetBuilder.registerWriteHandler(horizontalCellStyleStrategy);
        excelWriterSheetBuilder.registerWriteHandler(new SimpleColumnWidthStyleStrategy(20));
        for (CellWriteHandler cellWriteHandler : cellWriteHandlers) {
            excelWriterSheetBuilder.registerWriteHandler(cellWriteHandler);
        }
        WriteSheet sheet = excelWriterSheetBuilder.build();
        excelWriter.write(data, sheet);
    }

}
